# -*- coding: utf-8 -*-
from crm.models import Projects,Project_course_info
from crm.models import Agent
from crm.models import Salesperson
from crm.models import Members

from django.db import models

#学员信息表
#alter table crmv2_student modify university1 varchar(200) default '';
class Student(models.Model):
    name_en = models.CharField(max_length = 30)
    name_ch = models.CharField(max_length = 30,default='')
    first_name = models.CharField(max_length = 30,default='')
    last_name = models.CharField(max_length = 30,default='')
    fullname = models.CharField(max_length = 30,default='')
    tel_h = models.CharField(max_length = 20)
    tel_o = models.CharField(max_length = 20)
    tel_m = models.CharField(max_length = 20)
    email = models.CharField(max_length = 100)
    email2 = models.CharField(max_length = 100)
    address = models.CharField(max_length = 200)
    officeaddress = models.CharField(max_length = 200,default='')
    program = models.ForeignKey(Projects)
    applytime = models.DateTimeField(null = True)
    member = models.ForeignKey(Members, related_name = 'members', null = True)
    agent = models.ForeignKey(Agent, default = 1)
    salesperson = models.ForeignKey(Salesperson, null = True)
    graduated = models.BooleanField(default = 0)
    referee = models.ForeignKey(Members, null = True)
    student_id = models.CharField(max_length = 30,default='')
    appellation = models.CharField(max_length = 10,default='')
    postcode = models.CharField(max_length = 10,default='')
    country = models.CharField(max_length = 50,default='')
    title = models.CharField(max_length = 200,default='')
    employer = models.CharField(max_length = 200,default='')
    pic = models.CharField(max_length = 30,default='')
    application_date = models.DateField(null = True)
    application_date_str = models.CharField(max_length = 20,default='')
    acceptance_date = models.DateField(null = True)
    acceptance_date_str = models.CharField(max_length = 20,default='')
    start_date = models.DateField(null = True)
    start_date_str = models.CharField(max_length = 20,default='')
    program2 = models.CharField(max_length = 30,default='')
    sale2 = models.CharField(max_length = 30,default='')
    birthday = models.CharField(max_length = 12,default='')
    birthday_date = models.DateField(null = True)
    industry = models.CharField(max_length = 50,default='')
    mlevel = models.CharField(max_length = 20,default='')
    emsvalue = models.IntegerField(max_length = 2, default = 1)
    edu1 = models.CharField(max_length = 100,default='')
    edu2 = models.CharField(max_length = 100,default='')
    edu3 = models.CharField(max_length = 100,default='')
    awarding1 = models.CharField(max_length = 200,default='')
    awarding2 = models.CharField(max_length = 200,default='')
    awarding3 = models.CharField(max_length = 200,default='')
    university1 = models.CharField(max_length = 200,default='')
    university2 = models.CharField(max_length = 200,default='')
    university3 = models.CharField(max_length = 200,default='')
    attachcv = models.CharField(max_length = 50,default='')
    attachappform = models.CharField(max_length = 50,default='')
    remark = models.CharField(max_length = 4000,default='')
    def __unicode__(self):
        return self.name_en

#课程表,好像没用
class Course(models.Model):
    salesperson = models.ForeignKey(Projects)
    course_code = models.CharField(max_length = 30)
    foreign_course_code = models.CharField(max_length = 30)
    credit = models.FloatField(max_length = 3)

#学员申请的课程表.
#student enrolled program course record
class Program_student_course(models.Model):
    project = models.ForeignKey(Projects, default = '0')
    project_course_info_id = models.IntegerField(max_length = 11, default = 0)
    student = models.ForeignKey(Student, default = '0')
    period = models.CharField(max_length = 20, null = True, default = '')
    modules = models.CharField(max_length = 50, null = True, default = '')
    core_elective = models.CharField(max_length = 50, null = True, default = '')
    coursecode = models.CharField(max_length = 20, null = True, default = '')
    season = models.CharField(max_length = 20, null = True, default = '')
    year = models.CharField(max_length = 10, null = True, default = '')
    class_start = models.DateTimeField(null = True)
    class_end = models.DateTimeField(null = True)
    lecture = models.CharField(max_length = 100, null = True, default = '')
    textbook = models.TextField(null = True)
    venue = models.CharField(max_length = 50, null = True, default = '')
    attoch = models.CharField(max_length = 50, null = True, default = '')
    status = models.CharField(max_length = 20, null = True, default = '')
    dateofdefer = models.CharField(max_length = 20, null = True, default = '')
    pcid = models.IntegerField('model Project_course_info ID',max_length = 11, default = 0)
    is_removed = models.SmallIntegerField(max_length = 1, default = 0)
    class_date3 = models.DateTimeField(null = True)
    class_date4 = models.DateTimeField(null = True)
    class_date5 = models.DateTimeField(null = True)
    taiwankemu = models.CharField(max_length = 255, null = True, default = '')
    class Meta:
        db_table = 'program_student_course'
    def get_pci_obj (self):
        
        try:
            pci_obj=Project_course_info.objects.get(project=self.project,period=self.period)
        except:
            pci_obj=''
        
        #pci_obj=Project_course_info.objects.get(project=self.project,period=self.period)
        return pci_obj

#记录学员交费记录
#student payment record
class Student_Payment_Record(models.Model):
    project = models.ForeignKey(Projects, default = '0')
    student = models.ForeignKey(Student, default = '0')
    moneytype = models.CharField(max_length = 20, null = True, default = '')
    amount =models.CharField(max_length = 20, null = True, default = '')
    discount = models.CharField(max_length = 20, null = True, default = '')
    discount_money = models.CharField(max_length = 20, null = True, default = '')
    referredby = models.CharField(max_length = 100, null = True, default = '')
    module_wavied = models.CharField(max_length = 20, null = True, default = '')
    module_wavied_money = models.CharField(max_length = 20, null = True, default = '')
    subtotal_money = models.CharField(max_length = 20, null = True, default = '')
    date1 = models.DateTimeField(null = True)
    date2 = models.DateTimeField(null = True)
    date3 = models.DateTimeField(null = True)
    date4 = models.DateTimeField(null = True)
    date1_money = models.CharField(max_length = 20, null = True, default = '')
    date2_money = models.CharField(max_length = 20, null = True, default = '')
    date3_money = models.CharField(max_length = 20, null = True, default = '')
    date4_money = models.CharField(max_length = 20, null = True, default = '')
    date1_invoice = models.CharField(max_length = 20, null = True, default = '')
    date2_invoice = models.CharField(max_length = 20, null = True, default = '')
    date3_invoice = models.CharField(max_length = 20, null = True, default = '')
    date4_invoice = models.CharField(max_length = 20, null = True, default = '')
    date1_paid = models.DateTimeField(null = True)
    date2_paid = models.DateTimeField(null = True)
    date3_paid = models.DateTimeField(null = True)
    date4_paid = models.DateTimeField(null = True)
    date1_paiy_by = models.CharField(max_length = 20, null = True, default = '')
    date2_paiy_by = models.CharField(max_length = 20, null = True, default = '')
    date3_paiy_by = models.CharField(max_length = 20, null = True, default = '')
    date4_paiy_by = models.CharField(max_length = 20, null = True, default = '')
    date1_handed_by = models.CharField(max_length = 20, null = True, default = '')
    date2_handed_by = models.CharField(max_length = 20, null = True, default = '')
    date3_handed_by = models.CharField(max_length = 20, null = True, default = '')
    date4_handed_by = models.CharField(max_length = 20, null = True, default = '')
    moneytypedate1 = models.CharField(max_length = 20, null = True, default = '')
    moneytypedate2 = models.CharField(max_length = 20, null = True, default = '')
    moneytypedate3 = models.CharField(max_length = 20, null = True, default = '')
    moneytypedate4 = models.CharField(max_length = 20, null = True, default = '')
    datefee = models.CharField(max_length = 20, null = True, default = '')
    datefee_money = models.CharField(max_length = 20, null = True, default = '')
    datefee_invoice = models.CharField(max_length = 20, null = True, default = '')
    datefee_paid = models.CharField(max_length = 20, null = True, default = '')
    datefee_paiy_by = models.CharField(max_length = 20, null = True, default = '')
    datefee_handed_by = models.CharField(max_length = 20, null = True, default = '')
    moneytypedatefee = models.CharField(max_length = 20, null = True, default = '')
    datereg = models.CharField(max_length = 20, null = True, default = '')
    datereg_money = models.CharField(max_length = 20, null = True, default = '')
    datereg_invoice = models.CharField(max_length = 20, null = True, default = '')
    datereg_paid = models.CharField(max_length = 20, null = True, default = '')
    datereg_paiy_by = models.CharField(max_length = 20, null = True, default = '')
    datereg_handed_by = models.CharField(max_length = 20, null = True, default = '')
    moneytypedatereg = models.CharField(max_length = 20, null = True, default = '')
    is_removed = models.SmallIntegerField(max_length = 1, default = 0)
    fullpay = models.SmallIntegerField(max_length = 1, default = 0)
    class Meta:
        db_table = 'student_payment_record'
"""
CREATE TABLE `student_payment_record` (
  `id` int(11) NOT NULL auto_increment,
  `project_id` int(11) default '0',
  `student_id` int(11) default '0',
  `moneytype` varchar(20) collate utf8_bin default '',
  `amount` varchar(20) collate utf8_bin default '',
  `discount` varchar(20) collate utf8_bin default '',
  `discount_money` varchar(20) collate utf8_bin default '',
  `referredby` varchar(100) collate utf8_bin default '',
  `module_wavied` varchar(20) collate utf8_bin default '',
  `module_wavied_money` varchar(20) collate utf8_bin default '',
  `subtotal_money` varchar(20) collate utf8_bin default '',
  `date1` varchar(20) collate utf8_bin default '',
  `date2` varchar(20) collate utf8_bin default '',
  `date3` varchar(20) collate utf8_bin default '',
  `date4` varchar(20) collate utf8_bin default '',
  `date1_money` varchar(20) collate utf8_bin default '',
  `date2_money` varchar(20) collate utf8_bin default '',
  `date3_money` varchar(20) collate utf8_bin default '',
  `date4_money` varchar(20) collate utf8_bin default '',
  `date1_invoice` varchar(20) collate utf8_bin default '',
  `date2_invoice` varchar(20) collate utf8_bin default '',
  `date3_invoice` varchar(20) collate utf8_bin default '',
  `date4_invoice` varchar(20) collate utf8_bin default '',
  `date1_paid` varchar(20) collate utf8_bin default '',
  `date2_paid` varchar(20) collate utf8_bin default '',
  `date3_paid` varchar(20) collate utf8_bin default '',
  `date4_paid` varchar(20) collate utf8_bin default '',
  `date1_paiy_by` varchar(20) collate utf8_bin default '',
  `date2_paiy_by` varchar(20) collate utf8_bin default '',
  `date3_paiy_by` varchar(20) collate utf8_bin default '',
  `date4_paiy_by` varchar(20) collate utf8_bin default '',
  `date1_handed_by` varchar(20) collate utf8_bin default '',
  `date2_handed_by` varchar(20) collate utf8_bin default '',
  `date3_handed_by` varchar(20) collate utf8_bin default '',
  `date4_handed_by` varchar(20) collate utf8_bin default '',
  `is_removed` smallint(1) default '0',
  PRIMARY KEY  (`id`),
  KEY `project_id` (`project_id`),
  KEY `student_id` (`student_id`),
  KEY `moneytype` (`moneytype`),
  KEY `amount` (`amount`),
  KEY `discount` (`discount`),
  KEY `referredby` (`referredby`),
  KEY `module_wavied` (`module_wavied`),
  KEY `subtotal_money` (`subtotal_money`),
  KEY `is_removed` (`is_removed`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;



CREATE TABLE `program_student_course` (
  `id` int(11) NOT NULL auto_increment,
  `project_id` int(11) default '0',
  `student_id` int(11) default '0',
  `period` varchar(20) collate utf8_bin default '',
  `modules` varchar(50) collate utf8_bin default '',
  `core_elective` varchar(50) collate utf8_bin default '',
  `coursecode` varchar(20) collate utf8_bin default '',
  `season` varchar(20) collate utf8_bin default '',
  `year` varchar(10) collate utf8_bin default '',
  `class_start` varchar(20) collate utf8_bin default '',
  `class_end` varchar(20) collate utf8_bin default '',
  `lecture` varchar(100) collate utf8_bin default '',
  `textbook` varchar(100) collate utf8_bin default '',
  `venue` varchar(50) collate utf8_bin default '',
  `attoch` varchar(50) collate utf8_bin default '',
  `status` varchar(20) collate utf8_bin default '',
  `dateofdefer` varchar(20) collate utf8_bin default '',
  `is_removed` smallint(1) default '0',
  PRIMARY KEY  (`id`),
  KEY `project_id` (`project_id`),
  KEY `student_id` (`student_id`),
  KEY `period` (`period`),
  KEY `coursecode` (`coursecode`),
  KEY `modules` (`modules`),
  KEY `class_start` (`class_start`),
  KEY `class_end` (`class_end`),
  KEY `venue` (`venue`),
  KEY `status` (`status`),
  KEY `is_removed` (`is_removed`),
  KEY `season` (`season`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin 


alter table crmv2_student add column attachappform varchar(50) default '';


alter table crmv2_student add column first_name varchar(30) default '' after name_ch;
alter table crmv2_student add column last_name varchar(30) default '' after first_name;

alter table crmv2_student add column postcode varchar(10) default '';
alter table crmv2_student add column country varchar(50) default '';
alter table crmv2_student add column email2 varchar(100) default '' after email;
alter table crmv2_student add column title varchar(50) default '';
alter table crmv2_student add column employer varchar(50) default '';
alter table crmv2_student add column pic varchar(30) default '';

alter table crmv2_student add column enrollment_date date default NULL;

alter table crmv2_student add column fullname varchar(30) default '' after last_name;
alter table crmv2_student add column sale2 varchar(30) default '';

alter table crmv2_student add column industry varchar(50) default '';

alter table crmv2_student add column officeaddress varchar(200) default '' after address;

alter table crmv2_student add column emsvalue smallint(2) default 1;


alter table crmv2_student add column edu1 varchar(20) default '';

alter table crmv2_student add column awarding1 varchar(50) default '';

alter table crmv2_student add column university1 varchar(50) default '';



alter table crmv2_student add column birthday_date date default NULL after birthday;
alter table crmv2_student add column enrollment_date_str varchar(20) default '' after enrollment_date;

"""